﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using SpectationClient.DataBaseDescription;

using SpectationClient.SpectralTools;
using Npgsql;
using SpectationClient;
using SpectationClient.Stuff;
using SpectationClient.SQLCommandBuilder;
using SpectationClient.Async;

namespace SpectationClient.GUI {
    public partial class SubInsertObservationArea: Form {
        private DBManager dbm;
        private ErrorList EL;
        
        private UC_SetCoordinate uc_COORD;
        
        private Regex REGEX_ENDDOTS = new Regex("[.]+$", RegexOptions.Singleline);
        //private bool soil_calendar_changed;
        //private bool area_calendar_changed;
        private TableInfo ti_area;
        private AGetDatabaseObject AGDT;


        private bool SQL_INIT_CORRECT = true;
        private Dictionary<TableInfo, Control> initLUTComboBoxes = new Dictionary<TableInfo, Control>();
        private int sql_init_tables = 0;

        public SubInsertObservationArea() {
            InitializeComponent();
        }

        public SubInsertObservationArea(ref DBManager dbm) {
            InitializeComponent();

            this.Icon = Resources.Icon;
            this.Disposed +=new EventHandler(SubInsertObservationArea_Disposed);
            this.dbm = dbm;
            this.ti_area = dbm.getTableInfo("C_GFZ", "OBSERVATION_AREAS");
            EL = new ErrorList(ref btINSERT);
            AGDT = new AGetDatabaseObject();
            AGDT.GetDataTableCompleted += new GetDataTableCompletedEventHandler(AGDT_GetDataTableCompleted);
            
            
            timer.Enabled = false;
            timer.Tick += new EventHandler(timer_Tick);
            this.TSL_StatusLabel.Text = "";

            uc_COORD = new UC_SetCoordinate(ref dbm, Properties.Settings.Default.DEF_SRID, ref EL);
            this.Loc_Coord.Controls.Add(uc_COORD);
            uc_COORD.SRID_Changed += new EventHandler(VAL_Required);
            uc_COORD.ValueChanged += new EventHandler(VAL_Required);
            this.Loc_ID.TextChanged += new EventHandler(VAL_Required);
            this.Loc_Locname.TextChanged += new EventHandler(VAL_Required);
            this.Loc_Notes.TextChanged += new EventHandler(VAL_Required);
            this.Loc_Slope.TextChanged += new EventHandler(VAL_Required);
            this.Loc_Aspect.TextChanged += new EventHandler(VAL_Required);
            
            //Area
            this.area_gfz_id_tb.TextChanged += new EventHandler(VAL_Required);
            this.area_name_tb.TextChanged += new EventHandler(VAL_Required);
            this.area_tb_Bio_BB.TextChanged+= new EventHandler(VAL_Required);
            this.area_tb_Bio_EUNIS.TextChanged+= new EventHandler(VAL_Required);
            this.AREA_BFNCode_tb.TextChanged += new EventHandler(VAL_Required);
            this.area_tb_length.TextChanged += new EventHandler(VAL_Required);
            this.area_tb_width.TextChanged += new EventHandler(VAL_Required);
            this.area_tb_area.TextChanged += new EventHandler(VAL_Required);
            this.area_tb_Area_Notes.TextChanged+= new EventHandler(VAL_Required);
            this.area_tb_Methodic.TextChanged += new EventHandler(VAL_Required);
            this.area_cb_LU_CLC.SelectedValueChanged += new EventHandler(VAL_Required);
            this.area_cb_LU_KA5.SelectedValueChanged += new EventHandler(VAL_Required);
            this.area_cb_Reliefeinheit_KA5.SelectedValueChanged +=new EventHandler(VAL_Required);
            this.area_aspect_nosw.TextChanged += new EventHandler(VAL_Required);

            this.Soil_ge_bodenform_tb.TextChanged += new EventHandler(VAL_Required);
            this.Soil_colortext.TextChanged += new EventHandler(VAL_Required);
            this.Soil_GE_Bezeichnung.TextChanged += new EventHandler(VAL_Required);
            this.Soil_moisturetext_cb.SelectedValueChanged += new EventHandler(VAL_Required);
            this.Soil_munsell_chroma.TextChanged +=new EventHandler(VAL_Required);
            this.Soil_munsell_hue.TextChanged +=new EventHandler(VAL_Required);
            this.Soil_munsell_value.TextChanged +=new EventHandler(VAL_Required);
            this.Soil_notes.TextChanged +=new EventHandler(VAL_Required);
            this.soil_tb_id.TextChanged +=new EventHandler(VAL_Required);
            this.soil_tb_iddescription.TextChanged +=new EventHandler(VAL_Required);
            this.Soil_WRB_Qualifier.SelectedValueChanged +=new EventHandler(VAL_Required);
            this.Soil_WRB_SoilGroup.SelectedValueChanged +=new EventHandler(VAL_Required);
            this.Soil_WRB_Specifier.SelectedValueChanged +=new EventHandler(VAL_Required);
            this.soil_monthCalendar1.DateChanged += new DateRangeEventHandler(VAL_Required);

            this.gb_PS_dateCalendar.DateChanged +=new DateRangeEventHandler(VAL_Required);
            this.Veg_S_C_Crypto.TextChanged += new EventHandler(VAL_Required);
            this.Veg_S_C_Herbs.TextChanged += new EventHandler(VAL_Required);
            this.Veg_S_C_Litter.TextChanged += new EventHandler(VAL_Required);
            this.Veg_S_C_oSoil.TextChanged += new EventHandler(VAL_Required);
            this.Veg_S_C_Shrubs.TextChanged += new EventHandler(VAL_Required);
            this.Veg_S_C_Total.TextChanged += new EventHandler(VAL_Required);
            this.Veg_S_C_Trees1.TextChanged += new EventHandler(VAL_Required);
            this.Veg_S_C_Trees2.TextChanged += new EventHandler(VAL_Required);
            this.Veg_S_H_Herbs.TextChanged += new EventHandler(VAL_Required); //Height should be between 0 and 100 meters
            this.Veg_S_H_Shrubs.TextChanged += new EventHandler(VAL_Required);//Height should be between 0 and 100 meters
            this.Veg_S_H_Trees1.TextChanged += new EventHandler(VAL_Required);//Height should be between 0 and 100 meters
            this.Veg_S_H_Trees2.TextChanged += new EventHandler(VAL_Required);//Height should be between 0 and 100 meters
            this.Veg_S_NOS_Crypto.TextChanged += new EventHandler(VAL_Required);
            this.Veg_S_NOS_Herbs.TextChanged += new EventHandler(VAL_Required);
            this.Veg_S_NOS_Shrubs.TextChanged += new EventHandler(VAL_Required);
            this.Veg_S_NOS_Total.TextChanged += new EventHandler(VAL_Required);
            this.Veg_S_NOS_Trees1.TextChanged += new EventHandler(VAL_Required);
            this.Veg_S_NOS_Trees2.TextChanged += new EventHandler(VAL_Required);

            //External Location
            //gbLOC_new.Visible = rbLOC_New.Checked = false;
            //gbLOC_select.Visible = rbLOC_select.Checked = false;
            //rbLOC_none.Checked = rbLOC_select.Checked == rbLOC_New.Checked; 

            //External Soil 
            //soil_gb_new.Visible = soil_rb_new.Checked = false;
            //soil_gb_select.Visible = soil_rb_select.Checked = false;
            //soil_rb_none.Checked = soil_rb_new.Checked == soil_rb_select.Checked;
            FormHelper.setLinkAction_SwitchVisualization(rbLOC_New, gbLOC_new);
            FormHelper.setLinkAction_SwitchVisualization(rbLOC_select, gbLOC_select);
            FormHelper.setLinkAction_SwitchVisualization(gb_SOIL_rb_new, gb_SOIL_new);
            FormHelper.setLinkAction_SwitchVisualization(gb_SOIL_rb_select, gb_SOIL_select);
            FormHelper.setLinkAction_SwitchVisualization(gb_PS_rb_new, gb_PS_new);
            FormHelper.setLinkAction_SwitchVisualization(gb_PS_rb_select, gb_PS_select);

            rbLOC_none.Checked = true;
            gb_SOIL_rb_none.Checked = true;
            gb_PS_rb_none.Checked = true;

            this.initComboBoxes();
            // this.FormClosing += new FormClosingEventHandler(SubInsertObservationArea_FormClosing);
        }

        void SubInsertObservationArea_Disposed(object sender, EventArgs e) {
            AGDT.GetDataTableCompleted -= AGDT_GetDataTableCompleted;
        }

        void AGDT_GetDataTableCompleted(object sender, GetDataTableCompletedEventArgs e) {
            TableInfo ti = (TableInfo) e.UserState;
            
            if(e.Cancelled) {

            } else if(e.Error != null) {
                this.SQL_INIT_CORRECT = false;
                this.RTB.Text += TextHelper.Exception2String(e.Error);
            } else {
                ComboBox cb = (ComboBox)this.initLUTComboBoxes[ti];
                DataTable dtNew = new DataTable();
                //e.DataTable.Rows.InsertAt(e.DataTable.NewRow(), 0);
                FormHelper.initComboBoxItems(cb, e.DataTable, ti);
            }
            this.initLUTComboBoxes.Remove(ti);

            this.TSL_Progressbar.Value = sql_init_tables - initLUTComboBoxes.Count;
            if(this.initLUTComboBoxes.Count == 0) {
                this.timer.Stop();
                if(this.SQL_INIT_CORRECT) {
                    this.btINSERT.Enabled = true;
                    this.TSL_StatusLabel.Text = "Initialisierung abgeschlossen";
                } else {
                    this.TSL_StatusLabel.Text = "Initialisierung fehlgeschlagen";
                }
            } else { 

            }

        }

        

       
     


        /// <summary>
        /// This function loads required Data Base content into combo boxes
        /// </summary>
        private void initComboBoxes() {

            this.btINSERT.Enabled = false;
            this.timer.Start();
            this.TSL_StatusLabel.Text = "Initialisiere Datenbankinformationen";
            
            //init LookupTable
            DataBaseInfo DBI = this.dbm.getDataBaseInfo();
            this.initLUTComboBoxes.Add(DBI["CKEYS"]["CORINE_LANDCOVER"], area_cb_LU_CLC);
            this.initLUTComboBoxes.Add(DBI["CKEYS"]["GE_KA5_RELIEFEINHEITEN"], area_cb_Reliefeinheit_KA5);
            this.initLUTComboBoxes.Add(DBI["CKEYS"]["GE_KA5_NUTZUNG"], area_cb_LU_KA5);
            this.initLUTComboBoxes.Add(DBI["CKEYS"]["WRB_QUALIFIERS"], Soil_WRB_Qualifier);
            this.initLUTComboBoxes.Add(DBI["CKEYS"]["WRB_REFERENCESOILGROUPS"], Soil_WRB_SoilGroup);
            this.initLUTComboBoxes.Add(DBI["CKEYS"]["WRB_SPECIFIERS"], Soil_WRB_Specifier);
            this.initLUTComboBoxes.Add(DBI["CKEYS"]["GE_KA5_BODENFEUCHTE"], Soil_moisturetext_cb);

            this.TSL_Progressbar.Value = 0;
            this.TSL_Progressbar.Maximum = sql_init_tables += initLUTComboBoxes.Count;
            foreach(TableInfo ti in this.initLUTComboBoxes.Keys) {
                NpgsqlCommand cmdSelect = CommandBuilder.getSELECT(ti);
                cmdSelect.Connection = this.dbm.Connection;
                AGDT.GetDataTableAsync(cmdSelect, ti);
            }
        }

        void timer_Tick(object sender, EventArgs e) {
            String txt = TSL_StatusLabel.Text;
            if(txt != null) {
                Match m = this.REGEX_ENDDOTS.Match(txt);
                if(m.Length < 3) {
                    TSL_StatusLabel.Text += '.';
                } else {
                    TSL_StatusLabel.Text = txt.Remove(txt.Length - 3);
                }
            }
        }

        void BGW_INSERT_ProgressChanged(object sender, ProgressChangedEventArgs e) {
            TSL_Progressbar.Value = e.ProgressPercentage;
            if(e.UserState !=null) TSL_StatusLabel.Text = e.UserState.ToString();
        }

        

        void BGW_INSERT_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e) {
            this.timer.Stop();
            if(e.Error != null){
                
                MessageBox.Show(e.Error.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                TSL_StatusLabel.Text = e.Error.Message;
            } else {
                MessageBox.Show("Daten erfolgreich eingefügt", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                TSL_StatusLabel.Text = "Daten erfolgreich eingefügt";
                TSL_Progressbar.Value = 0;
                FormHelper.ResetControls(this);
            }
            this.Enabled = true;
        }

        void BGW_INSERT_DoWork(object sender, DoWorkEventArgs e) {
            BackgroundWorker meself = (BackgroundWorker)sender;
            BGW_ObservationAreas_InsertInfo bgi = (BGW_ObservationAreas_InsertInfo)e.Argument;
            meself.ReportProgress(20, "Opening connection to database");
            
            bgi.connection.Open();
            meself.ReportProgress(30, "Start transaction");
            NpgsqlTransaction trans = bgi.connection.BeginTransaction(IsolationLevel.Serializable);
            bool done = true;
            try {
                if(bgi.cmd_loc != null) {
                    meself.ReportProgress(30, "Insert data into CORE.LOCATIONS");
                    bgi.cmd_loc.Connection = bgi.connection;
                    bgi.cmd_loc.Transaction = trans;
                    Object temp = bgi.cmd_loc.ExecuteScalar();
                    bgi.gzfAreaRowValues.Add("id_location", temp);
                    
                }

                if(meself.CancellationPending) return;
                if(bgi.cmd_soil != null) {
                    meself.ReportProgress(40, "Insert data into C_GFZ.SOILS");
                    bgi.cmd_soil.Connection = bgi.connection;
                    bgi.cmd_soil.Transaction = trans;
                    Object temp = bgi.cmd_soil.ExecuteScalar();
                    bgi.gzfAreaRowValues.Add("id_soil", temp);
                    //id_soil = new DB_ID(temp, typeof(int));
                }
                if(meself.CancellationPending) return;
                if(bgi.cmd_plantsocieties != null) {
                    meself.ReportProgress(50, "Insert data into C_GFZ.PLANT_SOCIETIES");
                    bgi.cmd_plantsocieties.Connection = bgi.connection;
                    bgi.cmd_plantsocieties.Transaction = trans;
                    Object temp = bgi.cmd_plantsocieties.ExecuteScalar();
                    bgi.gzfAreaRowValues.Add("id_plant_society", temp);
                }

                if(meself.CancellationPending) return;
                NpgsqlCommand cmd = bgi.gzfAreaRowValues.getInsertCmd(); 
                cmd.Connection = bgi.connection;
                cmd.Transaction = trans;
                meself.ReportProgress(60, "Insert data into CORE.LOCATIONS");
                cmd.Prepare();

                if(meself.CancellationPending) return;
                //bgi.cmdTableOIDs.Prepare();
                meself.ReportProgress(70, "Execute command");
                cmd.Prepare();
                cmd.ExecuteNonQuery();
                meself.ReportProgress(90, "Commit command");

                if(meself.CancellationPending) return;

            } catch(Exception ex) {
                done = false;
                e.Cancel = true;
                throw ex;

            } finally {
                if(!done || meself.CancellationPending) {
                    trans.Rollback();
                } else {
                    trans.Commit();
                }
                bgi.connection.Close();
            } 
        }

       

        void VAL_Required(object sender, EventArgs e) {
            if(this.SQL_INIT_CORRECT && this.initLUTComboBoxes.Count == 0) {
                EL.removeAllErrors();

                //Validation Location id
                if(rbLOC_none.Checked) {
                    //
                } else if(rbLOC_select.Checked) {
                    //               
                    EL.validation_Int32(Loc_ID, true);
                } else if(rbLOC_New.Checked) {
                    uc_COORD.validate();
                    EL.validation_string(Loc_Locname, true);
                    EL.validation_Number(Loc_Aspect, false);
                    EL.validation_Number(Loc_Slope, false);
                }

                //Validation Observation Area
                EL.validation_string(area_gfz_id_tb, true);
                EL.validation_string(area_aspect_nosw, false);
                EL.validation_Number(area_tb_length, false);
                EL.validation_Number(area_tb_width, false);
                EL.validation_Number(area_tb_area, false);


                List<Object> needOneSet_LU = new List<object>();
                needOneSet_LU.Add(area_cb_LU_CLC);
                needOneSet_LU.Add(area_cb_LU_KA5);
                EL.validation_needAtLeastOneValue(needOneSet_LU);

                //Validation soil id
                if(gb_SOIL_rb_none.Checked) {
                    //none
                } else if(gb_SOIL_rb_select.Checked) {
                    EL.validation_Int32(soil_tb_id, true);
                } else if(gb_SOIL_rb_new.Checked) {
                    EL.validation_string(Soil_GE_Bezeichnung, false);
                    EL.validation_string(Soil_ge_bodenform_tb, false);
                    EL.validation_string(Soil_moisturetext_cb, 100, false);

                    //WRB
                    bool has_wrb = Soil_WRB_Qualifier.SelectedIndex > 0 ||
                                   Soil_WRB_SoilGroup.SelectedIndex > 0 ||
                                   Soil_WRB_Specifier.SelectedIndex > 0;
                    if(has_wrb) {
                        EL.validation_string(Soil_WRB_Qualifier, true);
                        EL.validation_string(Soil_WRB_SoilGroup, true);
                        EL.validation_string(Soil_WRB_Specifier, true);
                    }

                    //Munsell
                    bool has_munsell = Soil_munsell_chroma.TextLength > 0 ||
                                       Soil_munsell_hue.TextLength > 0 ||
                                       Soil_munsell_value.TextLength > 0;
                    if(has_munsell) {
                        EL.validation_Int32(Soil_munsell_chroma, true);
                        EL.validation_string(Soil_munsell_hue, true);
                        EL.validation_Int32(Soil_munsell_value, true);
                    }
                }

                //Validation plant society
                if(gb_PS_rb_none.Checked) { 
                    //none
                } else if(gb_PS_rb_select.Checked) {
                    //selected
                    EL.validation_Int32(gb_PS_select_tbID, true);
                } else if(gb_PS_rb_new.Checked) { 
                    //new
                    EL.validation_Number(Veg_S_C_Crypto, false, 0, 100);
                    EL.validation_Number(Veg_S_C_Herbs, false, 0, 100);
                    EL.validation_Number(Veg_S_C_Litter, false, 0, 100);
                    EL.validation_Number(Veg_S_C_oSoil, false, 0, 100);
                    EL.validation_Number(Veg_S_C_Shrubs, false, 0, 100);
                    EL.validation_Number(Veg_S_C_Total, false, 0, 100);
                    EL.validation_Number(Veg_S_C_Trees1, false, 0, 100);
                    EL.validation_Number(Veg_S_C_Trees2, false, 0, 100);
                    EL.validation_Number(Veg_S_H_Herbs, false, 0, 100);
                    EL.validation_Number(Veg_S_H_Shrubs, false, 0, 100);
                    EL.validation_Number(Veg_S_H_Trees1, false, 0, 100);
                    EL.validation_Number(Veg_S_H_Trees2, false, 0, 100);
                    EL.validation_Number(Veg_S_NOS_Crypto, false, 0, 100);
                    EL.validation_Number(Veg_S_NOS_Herbs, false, 0, 100);
                    EL.validation_Number(Veg_S_NOS_Shrubs, false, 0, 100);
                    EL.validation_Number(Veg_S_NOS_Total, false, 0, 100);
                    EL.validation_Number(Veg_S_NOS_Trees1, false, 0, 100);
                    EL.validation_Number(Veg_S_NOS_Trees2, false, 0, 100);
                }

                if(EL.isEmpty) {
                    btINSERT.Enabled = true;

                    List<NpgsqlCommand> cmdList = new List<NpgsqlCommand>();
                    try {

                        cmdList.Add(getSQL_LOC());
                        cmdList.Add(getSQL_SOIL());
                        cmdList.Add(getSQL_PlantSociety());
                        cmdList.Add(getAREA_Values().getInsertCmd());
                        RTB.Text = CommandBuilder.CommandToString(cmdList);

                    } catch(Exception ex) {
                        btINSERT.Enabled = false;
                        FormHelper.ShowErrorBox(ex);
                    }
                } else {
                    btINSERT.Enabled = false;
                    RTB.Text = "Bitte korrekte Werte eingeben";
                }
            } else {
                btINSERT.Enabled = false;
            }
        }

        private NpgsqlCommand getSQL_LOC() {
           
            if(rbLOC_New.Checked) {
                TableInfo ti = dbm.getTableInfo("CORE", "LOCATIONS");
                InsertValues rv = new InsertValues(ref ti);
                rv.Add("name", Loc_Locname);
                rv.Add("slope", Loc_Slope);
                rv.Add("aspect", Loc_Aspect);
                rv.Add("notes", Loc_Notes);
                if(uc_COORD.hasHeightValue) rv.Add("height", uc_COORD.HeightValue);
                if(uc_COORD.hasXYValues) {
                    rv.AddCoordinate("SHAPE", uc_COORD.XValue, uc_COORD.YValue, uc_COORD.SRID);
                }
                return rv.getInsertCmd(true);
            }
            return null;
        }



        private NpgsqlCommand getSQL_SOIL() {
            if(gb_SOIL_rb_new.Checked) {
                TableInfo ti = dbm.getTableInfo("C_GFZ", "SOILS");

                InsertValues rv = new InsertValues(ref ti);
                rv.Add("ge_bodenbezeichnung", Soil_GE_Bezeichnung);
                rv.Add("ge_bodenform", Soil_ge_bodenform_tb);
                rv.Add("color_notes", Soil_colortext);
                rv.Add("moisture_ka5", Soil_moisturetext_cb);
                rv.Add("munsell_hue", Soil_munsell_hue);
                rv.Add("munsell_value", Soil_munsell_value);
                rv.Add("munsell_chroma", Soil_munsell_chroma);
                rv.Add("notes", Soil_notes);
                if(!rv.IsEmpty) rv.Add("date", soil_monthCalendar1);

                NpgsqlCommand cmdTemp = rv.getInsertCmd(true);
                String cmdTempText = CommandBuilder.CommandToString(cmdTemp);
                return cmdTemp;
            }
            return null;
        }

        private NpgsqlCommand getSQL_PlantSociety() {
            
            if(gb_PS_rb_new.Checked) {
                TableInfo ti = dbm.getTableInfo("C_GFZ", "PLANT_SOCIETIES");
                InsertValues rv = new InsertValues(ref ti);
                rv.Add("nos_total", Veg_S_NOS_Total);
                rv.Add("nos_trees1", Veg_S_NOS_Trees1);
                rv.Add("nos_trees2", Veg_S_NOS_Trees2);
                rv.Add("nos_shrubs", Veg_S_NOS_Shrubs);
                rv.Add("nos_herbs", Veg_S_NOS_Herbs);
                rv.Add("nos_crypt", Veg_S_NOS_Crypto);

                rv.Add("h_herbs", Veg_S_H_Herbs);
                rv.Add("h_shrubs", Veg_S_H_Shrubs);
                rv.Add("h_trees1", Veg_S_H_Trees1);
                rv.Add("h_trees2", Veg_S_H_Trees2);

                rv.Add("c_total", Veg_S_C_Total);
                rv.Add("c_trees1", Veg_S_C_Trees1);
                rv.Add("c_trees2", Veg_S_C_Trees2);
                rv.Add("c_shrubs", Veg_S_C_Shrubs);
                rv.Add("c_herbs", Veg_S_C_Herbs);
                rv.Add("c_crypto", Veg_S_C_Crypto);
                rv.Add("c_litter", Veg_S_C_Litter);
                rv.Add("c_open_soil", Veg_S_C_oSoil);
                rv.Add("notes", Veg_S_Notes);
                if(!rv.IsEmpty) {
                    rv.Add("date", gb_PS_dateCalendar);
                }
               
                return rv.getInsertCmd(true);
            }
            return null;
        }


        private InsertValues getAREA_Values() {
            //float temp_f;
            TableInfo ti = dbm.getTableInfo("C_GFZ", "OBSERVATION_AREAS");
            InsertValues rv = new InsertValues(ref ti);
            rv.Add("gfz_area_id", area_gfz_id_tb);
            rv.Add("aspect", area_aspect_nosw);
            rv.Add("size_x", area_tb_length);
            rv.Add("size_y", area_tb_width);
            rv.Add("area_m2", area_tb_area);
            rv.Add("relief_ka5", area_cb_Reliefeinheit_KA5);
            rv.Add("lu_ka5", area_cb_LU_KA5);
            rv.Add("lu_clc", area_cb_LU_CLC);


            rv.AddID("id_ge_biotoptyp_bb", area_tb_Bio_BB, true);
            rv.AddID("id_eunis_habitat", area_tb_Bio_EUNIS, true);
            rv.AddID("id_bfn_code", AREA_BFNCode_tb, true);

            rv.Add("area_name", area_name_tb);
            rv.Add("method", area_tb_Methodic);
            rv.Add("notes", area_tb_Area_Notes);
            if(rbLOC_select.Checked) {
                rv.Add("id_location", Loc_ID);
            }
            if(gb_SOIL_rb_select.Checked) {
                rv.Add("id_soil", soil_tb_id);
            } 

            return rv;
    
        }

      
        private void btCANCEL_Click(object sender, EventArgs e) {
            this.DialogResult = DialogResult.Cancel;
            this.Close();           
        }

        private void Loc_ID_Select_bt_Click(object sender, EventArgs e) {
            FormHelper.setInfoFromSelectionTable(Loc_ID_description, Loc_ID, this.dbm, "CORE", "LOCATIONS", "Positionsangabe auswählen", "Übernehmen");
        }

        private void bt_Bio_BB_Click(object sender, EventArgs e) {
            FormHelper.setInfoFromSelectionTable(area_tb_Bio_BB, this.dbm, "CKEYS", "GE_BIOTOPTYP_BB", "Biotoptyp auswählen", "Übernehmen");
        }


        private void bt_Bio_EUNIS_Click(object sender, EventArgs e) {
            FormHelper.setInfoFromSelectionTable(area_tb_Bio_EUNIS, this.dbm, "CKEYS", "EUNIS_HABITATS", "EUNIS Habitat auswählen", "Übernehmen");
           
        }

        private bool isEmptyDataRow(DataRow row) {
            for (int i = 0; i < row.ItemArray.Length; i++) {
              if (row[i] != null &&
                  row[i] != DBNull.Value &&
                  row[i].ToString().Trim().Length != 0) {
                  return false;
                }
            }
            return true;
        }

        private void removeEmptyValues(ref Dictionary<String, object> dic) {
            List<String> toRemove = new List<string>();
            foreach(String s in dic.Keys) { 
                if(dic[s] == null || dic[s] == DBNull.Value ||
                   dic[s].GetType() == typeof(string) && ((string)dic[s]).Trim().Length == 0)
                    toRemove.Add(s);
            }
            foreach(String s in toRemove) {
                dic.Remove(s);
            }
        }

        private void monthCalendar1_DateSelected(object sender, DateRangeEventArgs e) {
            Console.WriteLine(soil_monthCalendar1.SelectionStart.ToShortDateString());
        }

        private void btINSERT_Click(object sender, EventArgs e) {
            try {
                //Insert
                BackgroundWorkerProgressBar BGWPB = new BackgroundWorkerProgressBar();
                BackgroundWorker BGW_INSERT = BGWPB.BackgroundWorker;
                BGW_INSERT.DoWork +=new DoWorkEventHandler(BGW_INSERT_DoWork);
                BGW_INSERT.RunWorkerCompleted +=new RunWorkerCompletedEventHandler(BGW_INSERT_RunWorkerCompleted);

                BGW_ObservationAreas_InsertInfo insertstruct = new BGW_ObservationAreas_InsertInfo();
                insertstruct.cmd_loc = getSQL_LOC();
                insertstruct.cmd_soil = getSQL_SOIL();
                insertstruct.cmd_plantsocieties = getSQL_PlantSociety(); 
                insertstruct.gzfAreaRowValues = getAREA_Values();
                insertstruct.ti_area = dbm.getTableInfo("C_GFZ", "OBSERVATION_AREAS");
                insertstruct.connection = this.dbm.Connection.Clone();
                insertstruct.error_msg = null;
                
                this.timer.Start();
                this.Enabled = false;
                BGW_INSERT.RunWorkerAsync(insertstruct);
                //Remove value

            } catch(Exception ex) {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void soil_bt_select_id_Click(object sender, EventArgs e) {
            FormHelper.setInfoFromSelectionTable(soil_tb_iddescription, soil_tb_id, dbm, "C_GFZ", "VIEW_SOILS",
                  "Bitte vorhandenen Bodeneintrag auswählen", "Boden übernehmen");
        }

        private void AREA_BFNCode_bt_Click(object sender, EventArgs e) {
            FormHelper.setInfoFromSelectionTable(AREA_BFNCode_tb, dbm, "CKEYS", "BFN_CODE",
                  "Bitte BfN Eintrag auswählen", "Eintrag übernehmen");
        }

        private void gb_PS_bt_select_Click(object sender, EventArgs e) {
            FormHelper.setInfoFromSelectionTable(gb_PS_select_tbIDdescription, gb_PS_select_tbID, dbm
                , "C_GFZ", "VIEW_PLANT_SOCIETIES",
                  "Bitte Pflanzengesellschaft auswählen", "Pflanzengesellschaft übernehmen");
        }

        private void bt_Bio_BB_remove_Click(object sender, EventArgs e) {
            FormHelper.removeInfoFromSelectionTable(area_tb_Bio_BB);
        }

        private void bt_Bio_EUNIS_btRemove_Click(object sender, EventArgs e) {
            FormHelper.removeInfoFromSelectionTable(area_tb_Bio_EUNIS);
        }

        private void AREA_BFNCode_bt_remove_Click(object sender, EventArgs e) {
            FormHelper.removeInfoFromSelectionTable(AREA_BFNCode_tb);
        }

        private void area_tb_Bio_BB_TextChanged(object sender, EventArgs e) {
            bt_Bio_BB_remove.Enabled = !String.IsNullOrWhiteSpace(area_tb_Bio_BB.Text);
        }

        private void area_tb_Bio_EUNIS_TextChanged(object sender, EventArgs e) {
            bt_Bio_EUNIS_btRemove.Enabled = !String.IsNullOrWhiteSpace(area_tb_Bio_EUNIS.Text);
        }

        private void AREA_BFNCode_tb_TextChanged(object sender, EventArgs e) {
            AREA_BFNCode_bt_remove.Enabled = !String.IsNullOrWhiteSpace(AREA_BFNCode_tb.Text);
        }

      

        
    }

    public struct BGW_ObservationAreas_InsertInfo {
        public NpgsqlCommand cmd_soil;
        public NpgsqlCommand cmd_loc;
        public NpgsqlCommand cmd_plantsocieties;
        public NpgsqlConnection connection;
        public InsertValues gzfAreaRowValues;
        public TableInfo ti_area;
        public String error_msg;
        public Exception ex;
    }
}
